SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
--exec p_msqtystartacc 2012,04,-99
alter       procedure p_msqtystartacc (
  @nyear int,
  @nmonth int,
  @filid int
)  as  

  declare @subjectid int,@newfilid int,@moneyid int
  declare @bmbala  numeric(18,2)
  declare @fbmbala numeric(18,2)
  declare @tmsum   numeric(18,2)
  declare @ftmsum  numeric(18,2)
  declare @bmqtybala  numeric(18,8)
  declare @temp_tab1 table(subjectid integer,
                           moneyid integer,
                           bmbala numeric(18,2),
                           fbmbala numeric(18,2) ,
                           bmqtybala numeric(18,8))

  declare @temp_tab2 table(subjectid integer,
                           moneyid integer,
                           tmsum numeric(18,2),
                           ftmsum numeric(18,2) )

begin
  set nocount on
  delete tft_msquantity where nyear=@nyear and nmonth=@nmonth  
  declare temp_tab2_cursor cursor local for select * from @temp_tab2
  declare temp_tab1_cursor cursor local for select * from @temp_tab1
  declare cur_fil cursor for select fi_filid from td_filiale where fi_filid=@filid or @filid =-99
  
  open cur_fil 
  fetch next from cur_fil
  into  @newfilid;
  while @@fetch_status = 0 
  begin   
    insert into tft_msquantity (subjectid,moneyid,nyear,nmonth,filid ,tmsumdebit,tmsumcredit,tmsum,
      ftmsumdebit,ftmsumcredit,ftmsum,tmqtysumdebit,tmqtysumcredit)
    select d.subjectid,d.moneyid,@nyear,@nmonth,@newfilid ,
      sum(dcdebitamt) tmsumdebit,sum(dccreditamt) tmsumcredit, sum((dcdebitamt-dccreditamt)*sign(abs(m.formertype-2002))) tmsum,
      sum(debitamt) ftmsumdebit,sum(creditamt) ftmsumcredit, sum((debitamt-creditamt)*sign(abs(m.formertype-2002))) ftmsum,
      sum(sign(abs(d.dodebit))*isnull(quantity,0)) tmqtysumdebit,sum((1-sign(abs(d.dodebit)))*isnull(quantity,0)) tmqtysumcredit
    from tfb_voucherdtl d
    inner join tfb_voucher m on m.voucherid=d.voucherid and m.filid =@newfilid
    inner join tfd_subject s on d.subjectid=s.subjectid
    where s.doqty=1
      and (d.voucherid<0 and (d.borderno=2 or d.borderno=3))
    group by  d.subjectid,d.moneyid

    delete from @temp_tab1
    insert into @temp_tab1
    select d.subjectid,d.moneyid,
        sum((s.isdebit*2-1)*(dcdebitamt+dccreditamt)) bmbala,
        sum((s.isdebit*2-1)*(debitamt+creditamt)) fbmbala,
        sum((s.isdebit*2-1)*quantity)  bmqtybala
    from tfb_voucherdtl d
    inner join tfb_voucher m on m.voucherid=d.voucherid and m.filid =@newfilid
    inner join tfd_subject s on d.subjectid=s.subjectid
    where s.doqty=1 and
        (d.voucherid<0 and d.borderno=1)
    group by d.subjectid,d.moneyid

    open  temp_tab1_cursor
    fetch next from  temp_tab1_cursor
    into  @subjectid,@moneyid,@bmbala,@fbmbala,@bmqtybala
    while @@fetch_status = 0
      begin
        update tft_msquantity set bmbala =@bmbala,fbmbala=@fbmbala,bmqtybala=@bmqtybala
        where subjectid = @subjectid and moneyid=@moneyid and nyear=@nyear and nmonth=@nmonth and filid=@newfilid
        fetch next from  temp_tab1_cursor
        into  @subjectid,@moneyid,@bmbala,@fbmbala,@bmqtybala
      end
    close temp_tab1_cursor

    delete from @temp_tab2
    insert into @temp_tab2
    select d.subjectid,d.moneyid,
      sum((s.isdebit*2-1)*(dcdebitamt)) tmsum,
      sum((s.isdebit*2-1)*(debitamt)) ftmsum
    from tfb_voucherdtl d
    inner join tfb_voucher m on m.voucherid=d.voucherid and m.filid =@newfilid
    inner join tfd_subject s on d.subjectid=s.subjectid
    where s.doqty=1 and
        (d.voucherid<0 and d.borderno=4)
    group by  d.subjectid,d.moneyid

    open  temp_tab2_cursor
    fetch next from  temp_tab2_cursor
      into  @subjectid,@moneyid,@tmsum,@ftmsum
    while @@fetch_status = 0
      begin
        update tft_msquantity set tmsum = @tmsum,ftmsum = @ftmsum
        where subjectid = @subjectid and moneyid=@moneyid and nyear=@nyear and nmonth=@nmonth and filid=@newfilid
        fetch next from  temp_tab2_cursor
        into  @subjectid,@moneyid,@tmsum,@ftmsum
      end
    close temp_tab2_cursor
    fetch next from  cur_fil
    into  @newfilid;
  end
  deallocate temp_tab2_cursor
  deallocate temp_tab1_cursor
  close cur_fil;
  deallocate  cur_fil;

end

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

